using System.Text;
using Microsoft.Extensions.Options;
using SPI.AgentServer.Configuration;
using SPI.AgentServer.Services.AI;
using SPI.AgentServer.Services.Metadata;

namespace SPI.AgentServer.Services.DatabaseLearning;

/// <summary>
/// 数据库结构学习服务
/// 负责让 AI 理解数据库的表结构、关系和业务含义
/// </summary>
public class DatabaseSchemaLearner : IDatabaseSchemaLearner
{
    private readonly ISchemaRegistry _schemaRegistry;
    private readonly IPromptManager _promptManager;
    private readonly ILogger<DatabaseSchemaLearner> _logger;
    private readonly DatabaseConfiguration _config;

    public DatabaseSchemaLearner(
        ISchemaRegistry schemaRegistry,
        IPromptManager promptManager,
        IOptions<DatabaseConfiguration> options,
        ILogger<DatabaseSchemaLearner> logger)
    {
        _schemaRegistry = schemaRegistry;
        _promptManager = promptManager;
        _config = options.Value;
        _logger = logger;
    }

    /// <summary>
    /// 生成数据库结构的自然语言描述，供 AI 理解
    /// </summary>
    public async Task<string> GenerateSchemaDescriptionAsync(string? databaseName = null)
    {
        _logger.LogInformation("开始生成数据库结构描述...");
        
        var sb = new StringBuilder();
        sb.AppendLine("=== 数据库结构说明 ===");
        sb.AppendLine();

        // 获取所有表
        var tables = await _schemaRegistry.ListTablesAsync(databaseName);
        _logger.LogInformation($"发现 {tables.Count} 个表");

        sb.AppendLine($"数据库包含 {tables.Count} 个表：");
        sb.AppendLine();

        foreach (var tableName in tables)
        {
            try
            {
                var schema = await _schemaRegistry.GetTableSchemaAsync(tableName, databaseName);
                if (schema != null)
                {
                    sb.AppendLine($"### 表：{schema.TableName}");
                    
                    // 添加表的中文说明（根据表名推测）
                    var tableDescription = GuessTableDescription(schema.TableName);
                    if (!string.IsNullOrEmpty(tableDescription))
                    {
                        sb.AppendLine($"说明：{tableDescription}");
                    }
                    
                    sb.AppendLine("字段：");
                    foreach (var column in schema.Columns)
                    {
                        var columnDesc = GuessColumnDescription(schema.TableName, column.ColumnName);
                        sb.AppendLine($"  - {column.ColumnName} ({column.DataType}): {columnDesc}");
                        
                        if (column.IsPrimaryKey)
                            sb.AppendLine($"    [主键]");
                        if (!column.IsNullable)
                            sb.AppendLine($"    [必填]");
                    }

                    // 生成示例查询
                    var samples = await _schemaRegistry.GetSampleSqlAsync(tableName, databaseName);
                    if (samples.Any())
                    {
                        sb.AppendLine("\n常用查询示例：");
                        foreach (var sample in samples.Take(3))
                        {
                            sb.AppendLine($"  {sample}");
                        }
                    }
                    
                    sb.AppendLine();
                }
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, $"无法获取表 {tableName} 的结构");
            }
        }

        // 添加表关系说明
        sb.AppendLine("=== 表关系说明 ===");
        sb.AppendLine(GenerateRelationshipDescription(tables));
        
        // 添加查询建议
        sb.AppendLine("\n=== 查询建议 ===");
        sb.AppendLine("1. 涉及日期的查询，注意使用合适的日期函数");
        sb.AppendLine("2. 统计查询时，考虑使用 GROUP BY 和聚合函数");
        sb.AppendLine("3. 查询学生相关信息时，可能需要关联多个表");

        var description = sb.ToString();
        _logger.LogInformation($"生成了 {description.Length} 字符的数据库描述");
        
        return description;
    }

    /// <summary>
    /// 根据表名推测表的业务含义
    /// </summary>
    private string GuessTableDescription(string tableName)
    {
        var lowerName = tableName.ToLower();
        
        var descriptions = new Dictionary<string, string>
        {
            ["student"] = "学生信息表",
            ["students"] = "学生信息表",
            ["teacher"] = "教师信息表",
            ["teachers"] = "教师信息表",
            ["course"] = "课程信息表",
            ["courses"] = "课程信息表",
            ["class"] = "班级信息表",
            ["classes"] = "班级信息表",
            ["grade"] = "成绩表",
            ["grades"] = "成绩表",
            ["score"] = "分数表",
            ["scores"] = "分数表",
            ["department"] = "院系信息表",
            ["departments"] = "院系信息表",
            ["major"] = "专业信息表",
            ["majors"] = "专业信息表",
            ["enrollment"] = "选课记录表",
            ["enrollments"] = "选课记录表",
            ["attendance"] = "考勤记录表",
            ["exam"] = "考试信息表",
            ["exams"] = "考试信息表",
            ["user"] = "用户信息表",
            ["users"] = "用户信息表",
            ["admin"] = "管理员信息表",
            ["admins"] = "管理员信息表"
        };

        foreach (var kvp in descriptions)
        {
            if (lowerName.Contains(kvp.Key))
            {
                return kvp.Value;
            }
        }

        return "";
    }

    /// <summary>
    /// 根据字段名推测字段含义
    /// </summary>
    private string GuessColumnDescription(string tableName, string columnName)
    {
        var lowerName = columnName.ToLower();
        
        var commonDescriptions = new Dictionary<string, string>
        {
            ["id"] = "唯一标识符",
            ["name"] = "姓名",
            ["student_name"] = "学生姓名",
            ["teacher_name"] = "教师姓名",
            ["course_name"] = "课程名称",
            ["class_name"] = "班级名称",
            ["student_id"] = "学生ID",
            ["teacher_id"] = "教师ID",
            ["course_id"] = "课程ID",
            ["class_id"] = "班级ID",
            ["age"] = "年龄",
            ["gender"] = "性别",
            ["sex"] = "性别",
            ["birthday"] = "出生日期",
            ["birth_date"] = "出生日期",
            ["phone"] = "电话号码",
            ["mobile"] = "手机号码",
            ["email"] = "电子邮件",
            ["address"] = "地址",
            ["score"] = "分数",
            ["grade"] = "成绩/年级",
            ["credit"] = "学分",
            ["credits"] = "学分",
            ["semester"] = "学期",
            ["year"] = "学年",
            ["status"] = "状态",
            ["created_at"] = "创建时间",
            ["updated_at"] = "更新时间",
            ["deleted_at"] = "删除时间",
            ["create_time"] = "创建时间",
            ["update_time"] = "更新时间",
            ["is_active"] = "是否激活",
            ["is_deleted"] = "是否删除",
            ["department"] = "院系",
            ["major"] = "专业",
            ["enrollment_date"] = "入学日期",
            ["graduation_date"] = "毕业日期",
            ["student_no"] = "学号",
            ["teacher_no"] = "教工号",
            ["course_code"] = "课程代码"
        };

        // 完全匹配
        if (commonDescriptions.ContainsKey(lowerName))
        {
            return commonDescriptions[lowerName];
        }

        // 部分匹配
        foreach (var kvp in commonDescriptions)
        {
            if (lowerName.Contains(kvp.Key))
            {
                return kvp.Value;
            }
        }

        // 根据后缀推测
        if (lowerName.EndsWith("_id")) return "关联ID";
        if (lowerName.EndsWith("_name")) return "名称";
        if (lowerName.EndsWith("_date")) return "日期";
        if (lowerName.EndsWith("_time")) return "时间";
        if (lowerName.EndsWith("_count")) return "数量";
        if (lowerName.EndsWith("_no") || lowerName.EndsWith("_num")) return "编号";

        return "字段";
    }

    /// <summary>
    /// 生成表关系描述
    /// </summary>
    private string GenerateRelationshipDescription(List<string> tables)
    {
        var sb = new StringBuilder();
        
        // 基于命名规则推测关系
        var hasStudent = tables.Any(t => t.ToLower().Contains("student"));
        var hasTeacher = tables.Any(t => t.ToLower().Contains("teacher"));
        var hasCourse = tables.Any(t => t.ToLower().Contains("course"));
        var hasGrade = tables.Any(t => t.ToLower().Contains("grade") || t.ToLower().Contains("score"));
        var hasEnrollment = tables.Any(t => t.ToLower().Contains("enrollment") || t.ToLower().Contains("选课"));

        if (hasStudent && hasCourse && hasGrade)
        {
            sb.AppendLine("- 学生、课程和成绩之间存在关联关系");
            sb.AppendLine("- 一个学生可以选修多门课程");
            sb.AppendLine("- 每个学生的每门课程都有对应的成绩");
        }

        if (hasTeacher && hasCourse)
        {
            sb.AppendLine("- 教师和课程之间存在授课关系");
            sb.AppendLine("- 一个教师可以教授多门课程");
        }

        if (hasEnrollment)
        {
            sb.AppendLine("- 选课表记录了学生选课的详细信息");
        }

        return sb.ToString();
    }

    /// <summary>
    /// 保存学习结果到文件（可选）
    /// </summary>
    public async Task SaveSchemaDescriptionAsync(string description, string? fileName = null)
    {
        fileName ??= $"schema_description_{DateTime.Now:yyyyMMdd_HHmmss}.txt";
        var filePath = Path.Combine("schema_docs", fileName);
        
        Directory.CreateDirectory("schema_docs");
        await File.WriteAllTextAsync(filePath, description);
        
        _logger.LogInformation($"数据库结构描述已保存到：{filePath}");
    }
    
    /// <summary>
    /// 学习特定的数据库表
    /// </summary>
    /// <param name="databaseName">数据库名称</param>
    /// <param name="specificTables">要学习的特定表列表</param>
    /// <returns>数据库结构的自然语言描述</returns>
    public async Task<string> LearnSpecificTablesAsync(string? databaseName, List<string> specificTables)
    {
        _logger.LogInformation($"开始学习特定表：{string.Join(", ", specificTables)}");
        
        var sb = new StringBuilder();
        sb.AppendLine("=== 数据库结构说明（特定表） ===");
        sb.AppendLine();
        sb.AppendLine($"数据库包含以下 {specificTables.Count} 个表：");
        sb.AppendLine();

        foreach (var tableName in specificTables)
        {
            try
            {
                var schema = await _schemaRegistry.GetTableSchemaAsync(tableName, databaseName);
                if (schema != null)
                {
                    sb.AppendLine($"### 表：{schema.TableName}");
                    
                    // 添加表的中文说明（根据表名推测）
                    var tableDescription = GuessTableDescription(schema.TableName);
                    if (!string.IsNullOrEmpty(tableDescription))
                    {
                        sb.AppendLine($"说明：{tableDescription}");
                    }
                    
                    sb.AppendLine("字段：");
                    foreach (var column in schema.Columns)
                    {
                        var columnDesc = GuessColumnDescription(schema.TableName, column.ColumnName);
                        sb.AppendLine($"  - {column.ColumnName} ({column.DataType}): {columnDesc}");
                        
                        if (column.IsPrimaryKey)
                            sb.AppendLine($"    [主键]");
                        if (!column.IsNullable)
                            sb.AppendLine($"    [必填]");
                    }

                    // 生成示例查询
                    var samples = await _schemaRegistry.GetSampleSqlAsync(tableName, databaseName);
                    if (samples.Any())
                    {
                        sb.AppendLine("\n常用查询示例：");
                        foreach (var sample in samples.Take(3))
                        {
                            sb.AppendLine($"  {sample}");
                        }
                    }
                    
                    sb.AppendLine();
                }
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, $"无法获取表 {tableName} 的结构");
                sb.AppendLine($"### 表：{tableName}");
                sb.AppendLine($"错误：无法获取此表的结构信息 - {ex.Message}");
                sb.AppendLine();
            }
        }

        // 添加表关系说明（仅针对特定表）
        sb.AppendLine("=== 表关系说明 ===");
        sb.AppendLine(GenerateRelationshipDescription(specificTables));
        
        // 添加查询建议
        sb.AppendLine("\n=== 查询建议 ===");
        sb.AppendLine("1. 这是部分表的结构，如需查询其他表请先学习");
        sb.AppendLine("2. 涉及日期的查询，注意使用合适的日期函数");
        sb.AppendLine("3. 统计查询时，考虑使用 GROUP BY 和聚合函数");

        var description = sb.ToString();
        _logger.LogInformation($"生成了 {description.Length} 字符的特定表描述");
        
        return description;
    }
}

public interface IDatabaseSchemaLearner
{
    Task<string> GenerateSchemaDescriptionAsync(string? databaseName = null);
    Task SaveSchemaDescriptionAsync(string description, string? fileName = null);
    Task<string> LearnSpecificTablesAsync(string? databaseName, List<string> specificTables);
}





